﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class BlackListDAL : IBlackListDAL
    {
        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];

        private const string SQL_SELECT_ACCOUNT = "SELECT * FROM BlackList WHERE BL_blackId = @blackId";

        public bool blackIdExists(int blackId)
        {
            bool isExist = false;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM BlackList WHERE BL_blackId = @blackId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@blackId", blackId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }

        /// <summary>
        /// 根据传入的userId判断一条数据是否存在
        /// </summary>       
        public bool userIdExists(int userId)
        {
            bool isExist = false;
            string SQL_SELECT_USER = "SELECT * FROM BlackList WHERE BL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_USER, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(int userId, int limitTime, System.DateTime timeStart, string reason)
        {
            bool opetation = false;
            string sqlStatement = "INSERT INTO BlackList VALUES(@userId,@limitTime,@timeStart, @reason)";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userId), new SqlParameter("@limitTime", limitTime), new SqlParameter("@timeStart", timeStart), new SqlParameter("@reason", reason) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

                /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(Model.BlackList blacklistitem)
        {
            bool opetation = false;
            string sqlStatement = "INSERT INTO BlackList VALUES(@userId, @limitTime, @timeStart,@reason)";
            Model.BlackList blacklist = blacklistitem;
            SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

            // SqlParameter[] paras;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@userId", blacklist.getBL_userId()), new SqlParameter("@limitTime", blacklist.getBL_limitTime()), new SqlParameter("@timeStart", blacklist.getBL_timeStart()), new SqlParameter("@reason", blacklist.getBL_reason()) };

            try
            {


                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(int blackId, int userId, int limitTime, System.DateTime timeStart, string reason)
        {
            bool opetation = false;
            string sqlStatement = "UPDATE BlackList SET BL_limitTime=@limitTime,BL_timeStart=@timeStart,BL_reason=@reason  WHERE BL_blackId = @blackId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@limitTime", limitTime), new SqlParameter("@timeStart", timeStart), new SqlParameter("@reason", reason), new SqlParameter("@blackId", blackId) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(Model.BlackList blacklistitem)
        {
            bool opetation = false;
            string sqlStatement = "UPDATE BlackList SET BL_userId=@userId,BL_limitTime=@limitTime,BL_timeStart=@timeStart,BL_reason=@reason WHERE BL_blackId = @blackId";
            Model.BlackList blacklist = blacklistitem;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);


                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@userId", blacklist.getBL_userId()), new SqlParameter("@limitTime", blacklist.getBL_limitTime()), new SqlParameter("@timeStart", blacklist.getBL_timeStart()), new SqlParameter("@reason", blacklist.getBL_reason()), new SqlParameter("@blackId", blacklist.getBL_blackId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 根据传入的accountID删除一条数据
        /// </summary>
        public bool blackIdDelete(int blackId)
        {
            bool opetation = false;
            string sqlStatement = "DELETE  FROM BlackList WHERE BL_blackId = @blackId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@blackId", blackId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 根据传入的userId删除一条数据
        /// </summary>
        public bool userIdDelete(int userId)
        {
            bool opetation = false;
            string sqlStatement = "DELETE FROM BlackList WHERE BL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 根据blackId得到一个对象实体
        /// </summary>
        public Model.BlackList GetModel(int blackId)
        {
            Model.BlackList blacklist = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@blackId", blackId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())

                        blacklist = new Model.BlackList(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToDateTime(rdr.GetDateTime(3)), rdr.GetString(4));
                    else
                        blacklist = new Model.BlackList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return blacklist;
        }
    }
}
